﻿Imports System.ComponentModel
Imports System.Data.SqlClient
Imports System.Web.Configuration
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports AjaxControlToolkit

Namespace Account
    ' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
    <System.Web.Script.Services.ScriptService()> _
    <System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
    <System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
    <ToolboxItem(False)> _
    Public Class Service
        Inherits System.Web.Services.WebService

        ''' <summary>
        ''' Returns a list of game names and ids contained within the database.
        ''' </summary>
        ''' <returns></returns>
        <WebMethod()> _
        Public Function GetGames() As CascadingDropDownNameValue()
            Dim values As New List(Of CascadingDropDownNameValue)()

            Dim connectionString As String = WebConfigurationManager.ConnectionStrings("SqlConnectionString").ConnectionString
            Dim connection As SqlConnection = New SqlConnection(connectionString)
            connection.Open()

            Try
                ' Populate the list with game ids and game names from the database.
                Dim command As SqlCommand = New SqlCommand("sp_public_getGames", connection)
                command.CommandType = CommandType.StoredProcedure
                Dim dr As SqlDataReader = command.ExecuteReader()
                While dr.Read()
                    values.Add(New CascadingDropDownNameValue(DirectCast(dr("Game_Name"), String), dr("Game_Id").ToString()))
                End While
            Catch ex As Exception

            Finally
                connection.Close()
            End Try

            ' Return the list of game names and game ids.
            Return values.ToArray
        End Function

        ''' <summary>
        ''' Returns a list of realm names and realm ids based on the chosen game from the database.
        ''' </summary>
        ''' <param name="knownCategoryValues">The known category values.</param>
        ''' <param name="category">The category.</param>
        ''' <returns></returns>
        <WebMethod()> _
        Public Function GetRealms(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
            Dim kv As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)

            Dim gameId As Integer = Nothing
            If Not kv.ContainsKey("Game") OrElse Not Int32.TryParse(kv("Game"), gameId) Then
                Return Nothing
            End If

            Dim values As New List(Of CascadingDropDownNameValue)

            Dim connectionString As String = WebConfigurationManager.ConnectionStrings("SqlConnectionString").ConnectionString
            Dim connection As SqlConnection = New SqlConnection(connectionString)
            connection.Open()

            Try
                ' Populate list with realm names and realm ids.
                Dim command As SqlCommand = New SqlCommand("sp_public_getRealmsByGame", connection)
                command.CommandType = CommandType.StoredProcedure
                command.Parameters.AddWithValue("@Game_Id", gameId)
                Dim dr As SqlDataReader = command.ExecuteReader()
                While dr.Read()
                    values.Add(New CascadingDropDownNameValue(DirectCast(dr("Realm_Name"), String), dr("Realm_Id").ToString()))
                End While
            Catch ex As Exception

            Finally
                connection.Close()
            End Try

            ' Return the list of realm names and realm ids.
            Return values.ToArray
        End Function

        ''' <summary>
        ''' Gets a list of factions based on a chosen game.
        ''' </summary>
        ''' <param name="knownCategoryValues">The known category values.</param>
        ''' <param name="category">The category.</param>
        ''' <returns></returns>
        <WebMethod()> _
        Public Function GetFactions(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
            '' These values are not tied directly to games rather realms.
            '' Need to pass selected game id to populate this drop down properly.
            '' Until then this list will not return properly filtered data so code placed 

            Dim kv As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)

            Dim realmId As Integer
            If Not kv.ContainsKey("Realm") OrElse Not Int32.TryParse(kv("Realm"), realmId) Then
                Return Nothing
            End If

            Dim values As New List(Of CascadingDropDownNameValue)()

            Dim connectionString As String = WebConfigurationManager.ConnectionStrings("SqlConnectionString").ConnectionString
            Dim connection As SqlConnection = New SqlConnection(connectionString)
            Dim command As SqlCommand = Nothing

            ' Get the Realm_Game value for the selected realm from the database.
            Dim gameId As Integer = Nothing
            connection.Open()
            Try
                command = New SqlCommand("sp_public_getGameFromRealm", connection)
                command.CommandType = CommandType.StoredProcedure
                command.Parameters.AddWithValue("@Realm_Id", realmId)
                command.Parameters.Add("@Realm_Game", SqlDbType.Int)
                command.Parameters("@Realm_Game").Direction = ParameterDirection.Output
                command.ExecuteNonQuery()
                gameId = CInt(command.Parameters("@Realm_Game").Value)
            Catch ex As Exception

            Finally
                connection.Close()
            End Try

            ' Populate list with faction names and faction ids.
            connection.Open()
            Try
                command = New SqlCommand("sp_public_getFactionsByGame", connection)
                command.CommandType = CommandType.StoredProcedure
                command.Parameters.AddWithValue("@Game_Id", gameId)
                Dim dr As SqlDataReader = command.ExecuteReader()
                While dr.Read()
                    values.Add(New CascadingDropDownNameValue(DirectCast(dr("Faction_Name"), String), dr("Faction_Id").ToString()))
                End While
            Catch ex As Exception

            Finally
                connection.Close()
            End Try

            ' Return the list of faction names and faction ids.
            Return values.ToArray
        End Function

        ''' <summary>
        ''' Returns a list of race names and race ids depending on the chosen faction from the database.
        ''' </summary>
        ''' <param name="knownCategoryValues">The known category values.</param>
        ''' <param name="category">The category.</param>
        ''' <returns></returns>
        <WebMethod()> _
        Public Function GetRaces(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
            Dim kv As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)

            Dim factionId As Integer
            If Not kv.ContainsKey("Faction") OrElse Not Int32.TryParse(kv("Faction"), factionId) Then
                Return Nothing
            End If

            Dim values As New List(Of CascadingDropDownNameValue)()

            Dim connectionString As String = WebConfigurationManager.ConnectionStrings("SqlConnectionString").ConnectionString
            Dim connection As SqlConnection = New SqlConnection(connectionString)
            connection.Open()

            Try
                ' Populate list with race names and race ids.
                Dim command As SqlCommand = New SqlCommand("sp_public_getRacesByFaction", connection)
                command.CommandType = CommandType.StoredProcedure
                command.Parameters.AddWithValue("@Faction_Id", factionId)
                Dim dr As SqlDataReader = command.ExecuteReader()
                While dr.Read()
                    values.Add(New CascadingDropDownNameValue(DirectCast(dr("Race_Name"), String), dr("Race_Id").ToString()))
                End While
            Catch ex As Exception

            Finally
                connection.Close()
            End Try

            ' Return the list of race names and race ids.
            Return values.ToArray
        End Function

        ''' <summary>
        ''' Gets a list of classes based on a chosen race.
        ''' </summary>
        ''' <param name="knownCategoryValues">The known category values.</param>
        ''' <param name="category">The category.</param>
        ''' <returns></returns>
        <WebMethod()> _
        Public Function GetClasses(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
            Dim kv As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)

            Dim raceId As Integer = Nothing
            If Not kv.ContainsKey("Race") OrElse Not Int32.TryParse(kv("Race"), raceId) Then
                Return Nothing
            End If

            Dim values As New List(Of CascadingDropDownNameValue)

            Dim connectionString As String = WebConfigurationManager.ConnectionStrings("SqlConnectionString").ConnectionString
            Dim connection As SqlConnection = New SqlConnection(connectionString)
            connection.Open()

            Try
                ' Populate list with class names and realm ids.
                Dim command As SqlCommand = New SqlCommand("sp_public_getClassesByRace", connection)
                command.CommandType = CommandType.StoredProcedure
                command.Parameters.AddWithValue("@Race_Id", raceId)
                Dim dr As SqlDataReader = command.ExecuteReader()
                While dr.Read()
                    values.Add(New CascadingDropDownNameValue(DirectCast(dr("Class_Name"), String), dr("Class_Id").ToString()))
                End While
            Catch ex As Exception

            Finally
                connection.Close()
            End Try

            ' Return the list of class names and class ids.
            Return values.ToArray
        End Function

        ''' <summary>
        ''' Gets a list of genders based on a chosen race.
        ''' </summary>
        ''' <param name="knownCategoryValues">The known category values.</param>
        ''' <param name="category">The category.</param>
        ''' <returns></returns>
        <WebMethod()> _
        Public Function GetGenders(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
            Dim kv As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)

            Dim raceId As Integer = Nothing
            If Not kv.ContainsKey("Race") OrElse Not Int32.TryParse(kv("Race"), raceId) Then
                Return Nothing
            End If

            Dim values As New List(Of CascadingDropDownNameValue)

            Dim connectionString As String = WebConfigurationManager.ConnectionStrings("SqlConnectionString").ConnectionString
            Dim connection As SqlConnection = New SqlConnection(connectionString)
            connection.Open()

            Try
                ' Populate list with gender names and gender ids.
                Dim command As SqlCommand = New SqlCommand("sp_public_getGendersByRace", connection)
                command.CommandType = CommandType.StoredProcedure
                command.Parameters.AddWithValue("@Race_Id", raceId)
                Dim dr As SqlDataReader = command.ExecuteReader()
                While dr.Read()
                    values.Add(New CascadingDropDownNameValue(DirectCast(dr("Gender_Name"), String), dr("Gender_Id").ToString()))
                End While
            Catch ex As Exception

            Finally
                connection.Close()
            End Try

            ' Return the list of realm names and realm ids.
            Return values.ToArray
        End Function

    End Class
End Namespace